BigQuery クエリ
リテラル等の仕様
その場でデータを作ってクエリする
動作確認に便利
code:struct.sql
SELECT MIN(status)
FROM UNNEST([
STRUCT('unexamined' AS status),
STRUCT('unexamined' AS status),
STRUCT('ng' AS status)
])
型ほしい時は型を書く
code:complex_struct.sql
SELECT * FROM UNNEST(
ARRAY<STRUCT<count INT64, time TIMESTAMP>>[
STRUCT(3, TIMESTAMP "2020-07-01 10:00:00"),
STRUCT(5, TIMESTAMP "2020-07-02 11:33:00"),
STRUCT(7, TIMESTAMP "2020-6-02 21:01:00")
])
WITH
WITH hoge AS (<query>) で宣言できる
サブクエリをネストさせずに WITH で切り出したりすると読みやすくなる
複数書くとき WITH hoge AS (...), fuga AS (...)
WITH の中に WITH も書ける
変数宣言のように使うパターン
code:variables
WITH vars AS (
SELECT
DATE "2020-06-01" AS since,
DATE "2020-06-30" AS until
)
SELECT * FROM daily_metrics, vars
WHERE vars.since <= date OR date <= vars.until
日付
1週間前を作る DATE_SUB(CURRENT_DATE, INTERVAL 1 WEEK);
24時間前 TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
DATE_*, TIME_*, TIMESTAMP_* などいろいろな関数がある
時間(HOUR)を取り出す EXTRACT(HOUR FROM created_at)
MINUTE などなんでも, GROUP BY して COUNT したりするのに便利
曜日 EXTRACT(DAYOFWEEK FROM DATETIME(timestamp, 'Asia/Tokyo'))
あまり普段使わない TIME 型、秒数にするにはゼロ値との差を秒数でとる
TIME_DIFF(time_col, TIME "00:00:00", SECOND)
TIME_DIFF(time_col, TIME "00:00:00", MICROSECOND) / 1000000.0 小数点以下の精度もほしいときに
文字列
分割して n 番目のフィールドを取る
SPLIT & OFFSET
code:split
FROM UNNEST([
STRUCT("foo,bar,baz" as tag),
STRUCT("hoge,fuga,piyo" as tag)
])
URL を切り出す
NET.HOST などでホスト部だけ切り出せる、その他の命令もいろいろある
いろいろ
REGEXP_EXTRACT(httpRequest.requestUrl, '//[^/]+(.+)$') AS pathquery
REGEXP_EXTRACT(httpRequest.requestUrl, '//[^/]+([^?#]+)') AS path
SPLIT(REGEXP_EXTRACT(httpRequest.requestUrl, '//[^/]+/([^?#]+)'), '/')[SAFE_OFFSET(1)] AS path_1st
REGEXP_EXTRACT(httpRequest.requestUrl, '(?:\?|&)key=([^&]*)') as params_key
IFNULL より COALESCE
複数の候補にフォールバックできる
つづりがわからなくなるのが難点
CONTAINS_SUBSTR は定数のみ
DREMELGOOGLESQL:CONTAINS_SUBSTR must be a constant expression at ... エラーが出る
しかし LIKE CONCAT('%', col ,'%') はできる
Array
配列の要素の参照
SELECT items[OFFSET(0)] FROM UNNEST([STRUCT([1,2,3] AS items)])
長さ不明なら SAFE_OFFSET を使うほうがよい(out of bounds エラーではなく null が返る)
長さ
SELECT ARRAY_LENGTH(items) FROM UNNEST([STRUCT([1,2,3] AS items)])
SELECT 句のサブクエリで複数行集める
一度 ARRAY にすればよい、OFFSET で任意の位置もとれる
SELECT ... ARRAY(SELECT ...) AS items
展開してクエリ
SELECT ... FROM table, UNNEST(column) AS item で配列を展開して item を条件にクエリ書くのをよくやる
パラメータ化されたクエリで ARRAY を渡して WHERE IN
WHERE column IN UNNEST(@array)
GROUP BY した中から最新の1つ
ARRAY_AGG して1つ目を取る
例えば投稿(post)ごとの最新のコメントをとるとして
SELECT ARRAY_AGG(comment ORDER BY commented_at DESC)[SAFE_OFFSET(0)] ... GROUP BY post_id
col を GROUP BY に含めたくないが FIRST_VALUE(col) OVER (PARTITION BY ...) したい
しかし which is neither grouped nor aggregated エラーが出る、という場合に有用
集計関数
COUNT, MAX, ANY_VALUE や ARRAY_AGG は 集計関数 なので GROUP BY したグループごとに作用する ARRAY_AGG(DISTINCT hoge IGNORE NULLS ORDER BY fuga LIMIT 3) など色々書ける
GROUP BY した各グループに作用するわけではない、GROUP BY で指定していないカラムを参照すると ... which is neither grouped nor aggregated と怒られる。
評価順は、その他の関数 → (集計関数 → HAVING)(GROUP BY した結果に作用するやつら) → ウィンドウ関数(ナビゲーション関数含む) になる
集計関数に OVER をつける(MAX ... OVER (...))とウィンドウ関数扱い
ARRAY_AGG 内でさらにフィルタする & STRUCT に詰めて unwrap する
CASE 書けるのでいろいろできる & 多値を返したければ STRUCT に入れて .* すればよい
code:filter_clothes.sql
-- 服の販売データ、服の種類と色と売れた日
WITH data AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<clothe STRING, color STRING, sold_at DATE>>[
STRUCT("shirt", "black", "2023-01-01"),
STRUCT("shirt", "black", "2023-01-02"),
STRUCT("shirt", "red", "2023-01-03"),
STRUCT("shirt", "pink", "2023-01-04"),
STRUCT("pants", "black", "2023-01-05"),
STRUCT("pants", "blue", "2023-01-06"),
STRUCT("jacket", "black", "2023-01-07")
])
)
-- 例: 服ごとに、黒以外で一番最初に売れた色と日を返したい
SELECT
clothe,
ARRAY_AGG(
CASE
-- 条件に合う(黒以外)ときに複数の値で STRUCT で名前付けつつ返す
WHEN color != "black" THEN STRUCT(color AS color, sold_at AS sold_at)
-- そうでないなら NULL
ELSE NULL
END
IGNORE NULLS -- IGNORE NULLS で条件に合わないものを読み飛ばす
ORDER BY sold_at ASC
FROM data
GROUP BY clothe
STRUCT(...) でその場で作っている、なんか型合わなかったりするなら
STRUCT<color STRING, sold_at DATE>(color, sold_at) のように明に書いてもいい
いやこれは WHERE color != "black" した対象を絞り込むほうが簡単でしょ、というのはそう
SELECT ARRAY OF STRUCT
グループごとにカウントする
SELECT ARRAY(SELECT AS STRUCT name, COUNT(*) as count FROM data GROUP BY name) as counts
例えば曜日別の行数を出したい
code:couby_by_dayofweek.sql
// EXTRACT(DAYOFWEEK FROM DATETIME(timestamp, 'Asia/Tokyo')) AS wday
SELECT
// "wday": 1, "count": 123 }, ...
ARRAY(
SELECT AS STRUCT
wday,
COUNT(*) AS count
FROM requests
GROUP BY dayofweek
) AS count_by_dayofweek
FROM ...
更にその集計をしたい
code:query.sql
WITH data AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<clothe STRING, color STRING>>[
STRUCT("shirt", "black"),
STRUCT("shirt", "black"),
STRUCT("shirt", "red"),
STRUCT("pants", "blue"),
STRUCT("pants", "black")
])
)
SELECT
clothe,
ARRAY_AGG(counts) as counts
FROM (
SELECT
clothe,
STRUCT(color, COUNT(color) as count) as counts
FROM data GROUP BY clothe, color
)
GROUP BY clothe
UNNEST せずに SELECT で特定の STRUCT を読む
普段カラム並べるところで select したらいい
code:select_array_of_struct
WITH data AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<id INT, attributes ARRAY<STRUCT<key STRING, value STRING>>>>[
])
)
SELECT (SELECT value FROM UNNEST(attributes) WHERE key = "name") as name FROM data;
UNNEST しつつ index を振る
UNNEST(array) AS val WITH OFFSET AS idx
各行の ARRAY 中に特定の値が含まれているか
WIP
WHERE 句の中でやる
code:in-where.sql
-- ARRAY<STRUCT<field STRING>> みたいな構造を仮定
AND EXISTS (SELECT 1 FROM UNNEST(array_column) WHERE field = "value")
SELECT 句でやって外側のクエリでフィルタする
ARRAY<STRING> とかなら IN でやる例を出す
WHERE "hoge" IN UNNEST(array_column)
NULL でなく空の結果がほしい
SELECT * FROM UNNEST([])
なんか LIMIT 0 と同じ?
JSON
JSON_QUERY と JSON_EXTRACT
JSON_EXTRACT / JSON_QUERY
JSON_EXTRACT_SCALAR / JSON_VALUE
キーに . が入ったオブジェクトを参照する時のエスケープが違う
JSON_EXTRACT(json, "$['s3.bucket']") と JSON_QUERY(json, '$."s3.bucket"')
JSON_EXTRACT はキーをダブルクオートで囲めない
まあどっちでもいいけど、新しい方(JSON_QUERY, JSON_VALUE) を使うほうがよい
JOIN
LIKE で JOIN すればいい状況はしばしばある、あまり OLTP 世界観では出てこない発想
分析関数
LOGICAL_OR, LOGICAL_AND
かく
ウィンドウ関数
OVER のあるやつ
ウィンドウフレーム = PARTITION BY
ROWS や RANGE を省略した場合はウィンドウフレーム = パーティション
ORDER BY 句も Window Frame 句も存在しない場合、ウィンドウ フレームにはそのパーティション内のすべての行が含まれます。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ORDER BY がある場合 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ORDER BY で並べて最初から現在行まで
末尾まで(... AND UNBOUNDED FOLLOWING)だと勘違いしているとおかしくなる
ROWS は前後の行を指定してウィンドウフレームを作る
RANGE は ORDER BY の値に加算減算して範囲を定義する
ORDER BY: パーティション内の行の順序を定義します。ほとんどの場合、この句は省略可能ですが
SUM(hoge) は SUM(hoge) OVER() と見なせばまあ使ってない
WINDOW name AS (PARTITION BY ... ORDER BY ...) で名前が付けられる
同じ PARTITION 句が続いた時にクエリ末尾にまとめられる(IF で WINDOW 切り替えれたりする?)
FIRST_VALUE / ANY VALUE
FIRST_VALUE(column IGNORE NULLS) など便利
? IGNORE した結果も NULL なら NULL になる? か確認
グループ内の重複除去
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) as row_num してフィルタ
PARTITION BY col1 で col1 が共通なものの中で並べ替えして ROW_NUMBER で番号を降る
code:row_number
SELECT * EXCEPT(num) FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY col1, ... ORDER BY col2 DESC) as row_num,
FROM ...
) WHERE row_num = 1;
RANK() が紹介されがちだけど、PARTITION BY カラムも並べ替えのカラムも同値の場合、同じ順位ということで重複した数字が振られる、大抵の場合 ROW_NUMBER() を使うのが意図した集計だろう
今は QUALIFY 句でできる(preview)
code:row_number_qualify
SELECT
*
FROM ...
WHERE true
QUALIFY ROW_NUMBER() OVER (PARTITION BY col1, ... ORDER BY col2 DESC) = 1;
WHERE, GROUP BY, HAVING いずれかが入っていないと使えない
フィルタ条件ない場合は WHERE true でいい → 無かったら勝手にこれ挿入してよ
ウィンドウ関数の結果でフィルタするためにサブクエリにしたり、EXCEPT してフィルタ用カラムを除いたりしなくていい
カラム名の定義順が違うテーブルを UNION したい!!
1回諦めてカラム名を列挙する
code:list_columns.sql
SELECT
ARRAY_TO_STRING(ARRAY_AGG(column_name ORDER BY ordinal_position), ',\n')
FROM
project.dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "first_table"
とかで
2 ↑ + scripting で
やってないけどできると思う
DDL
外部テーブルを作れる
Scripting
デバッグどうする?
なんか一時テーブルにログ書きたい / さっとデータ入れたい
table を SELECT して持っておく
ARRAY(SELECT AS STRUCT ...) する
code:select_as_struct.sql
DECLARE mytable DEFAULT (
ARRAY(SELECT AS STRUCT id, name FROM user ...)
);
SELECT * FROM UNNEST(mytable) AS t ...
よくある集計テク
CASE WHEN によるフィルタ
1つのクエリ内で複数の条件でフィルタして値を集計したい場合に使える
WHERE 句は複数書けないが COUNT は複数書ける、NULL は COUNT に含まれないことを使ってこう書ける
code:case-when.sql
SELECT
COUNT(*) as total,
COUNT(CASE WHEN gender IS "male" THEN 1 ELSE null END) as male,
COUNT(CASE WHEN gender IS "female" THEN 1 ELSE null END) as female,
FROM ...
行を数える場合、SUM より COUNT のほうがよい、全て NULL の場合 SUM() は NULL を返すけど COUNT は 0 を返す
MAX(IF(...)) によるフィルタ
WIP
SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
複数の COUNT などをまとめてみたい
code:double_query.sql
SELECT
(SELECT COUNT(*) FROM ...) as hoge,
(SELECT COUNT(*) FROM ...) as fuga
カラム名のところに () で囲んでクエリ書けばよかった、カウント同士の割り算もできる
出力が1行でないクエリ同士は UNION ALL などでくっつける(が、順序やカラム名がわからなくなるので微妙)
timestamp ごとのログ行数を数える
日付ごと
code:by_date
SELECT COUNT(*), DATE(timestamp) as date
FROM {table} GROUP BY date ORDER BY date
(日付, 時間)ごと
code:by_date_and_hour
SELECT COUNT(*), DATE(timestamp) as date, EXTRACT(HOUR FROM timestamp) as hour
FROM {table} GROUP BY date, hour ORDER BY date, hour`
AS でつけた別名で集計する
COUNT AS 系クエリをもう1つの SELECT でラップすれば読みやすい
* も SELECT すると元データもそのまま結果に出力できる
code:totaling_with_as
SELECT
*,
foo/total as foo_rate
FROM (
SELECT
COUNT(*) as total,
COUNT(CASE WHEN jsonPayload.foo IS TRUE THEN 1 ELSE NULL) as foo
FROM ...
);
APPROX_QUANTILES(col, n)
col 列を小さい順に n 個のバケットに振り分けて n+1 の長さの配列を返す
中央値: APPROX_QUANTILES(x, 2)[OFFSET(1)]
90%tile: APPROX_QUANTILES(x, 100)[OFFSET(90)]
テーブル
条件を元に SELECT するテーブルを切り替える
条件 + UNION ALL で一方を空にする、スキャン量減るかは見てない
code:switch-table.sql
WITH params AS (
SELECT TRUE AS cond -- 条件
), table1 AS (
SELECT 1 AS n
), table2 AS (
SELECT 2 AS n
)
SELECT
* EXCEPT(cond)
FROM
table1, params
WHERE
params.cond = TRUE
UNION ALL
SELECT
* EXCEPT(cond)
FROM
table2, params
WHERE
params.cond = FALSE
SELECT IF TABEL EXISTS
SELECT * FROM dataset.* WHERE _TABLE_SUFFIX = "table" でいける
クエリの実行時に実際にワイルドカードテーブルである必要はなく、普通のテーブルでも使える